if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_receive_schedule_sync')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_receive_schedule_sync
GO
CREATE PROCEDURE bd_proc_receive_schedule_sync
	@bh varchar(max),
	@ip varchar(200),
	@operator varchar(200)
as
BEGIN
--导入排班
-- 关闭打印受影响行数
SET NOCOUNT ON;

-- 定义返回受影响行数结果
	declare @result int = 0;


	DECLARE @yearMonth varchar(7);

	--  异常扑捉机制
		BEGIN TRY

			CREATE TABLE #returnTable(
				id INT IDENTITY,
				user_no varchar(100) COLLATE Chinese_PRC_CI_AS,--工号
				user_name varchar(100) COLLATE Chinese_PRC_CI_AS,--姓名
				msg varchar(1000) COLLATE Chinese_PRC_CI_AS--错误信息
			)
			CREATE TABLE #scheduleTable(
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
				user_no varchar(200) COLLATE Chinese_PRC_CI_AS,
			  fh VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,--id
			  banci_mc VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,--编号
			  col_name VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
			  date DATE
			)

			CREATE TABLE #result(
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  shift_id int,--id
			  shift_bh VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,--编号
			  col_name VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
			  value VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
			  date DATE,
			  year_month VARCHAR(10) COLLATE Chinese_PRC_CI_AS,
			  shift_is_rest int
			)
		-- 开启事务
			BEGIN TRAN

			--设置导入的月份
				select top 1 @yearMonth = rq from bd_schedule_receive where bh = @bh;

				--过滤不存在的人

				insert into #returnTable(user_no,user_name,msg)
				select r.user_no,r.user_name,'<该人员不存在>'  from bd_schedule_receive r
				left join dt_user u on u.user_no = r.user_no
				where u.user_no is null  and r.bh = @bh;
				--移除不存在的人员
				delete from bd_schedule_receive where bh = @bh and user_no in (select user_no from #returnTable);

				--过滤班次不存在的人员

				insert into #scheduleTable(user_serial,user_no,fh,banci_mc,col_name,date)
				select u.user_serial,b.user_no,b.fh,b.banci_mc,b.col_name,b.rq from(
				SELECT  a.user_no,
							case when left(a.banci,3) = '[X]' then '[X]' else '[A]' end  as fh ,
							case when left(a.banci,3) = '[X]' then substring(a.banci,4,len(a.banci))  else a.banci end  as banci_mc ,
							dateadd(day,SUBSTRING(a.riqi, 2, LEN(a.riqi))-1,a.rq+'-01') as rq,
							a.riqi as col_name
					FROM bd_schedule_receive
					UNPIVOT(banci FOR riqi IN(d1,d2,d3,d4,d5,d6,d7,d8,d9,d10,d11,d12,d13,d14,d15,d16,d17,d18,d19,d20,d21,d22,d23,d24,d25,d26,d27,d28,d29,d30,d31))a
					where bh = @bh) b
					INNER JOIN dt_user u on u.user_no = b.user_no

			 --过滤所排班次不存在的人
			 insert into #returnTable(user_no,user_name,msg)
				select r.user_no,r.user_name,'<该人员所排班次有些不存在或班次中未定义上下班时段>'  from bd_schedule_receive r
				inner join (
					select DISTINCT a.user_no from
						(select s.user_no,s.fh,zb.bh as zbbh,zs.bh as zsbh from 	#scheduleTable s
						LEFT JOIN zt_banci zb on zb.mc = s.banci_mc
						LEFT JOIN zt_shid zs on zb.bh = zs.parent and zs.lx=0) a
					where (a.zbbh is null or a.zsbh is null) and a.fh='[A]') b on b.user_no = r.user_no
				where r.bh = @bh


				--移除不存在的人员
				delete from #scheduleTable where user_no in (select user_no from #returnTable);

				--过滤已封账的日期的人员
				insert into #returnTable(user_no,user_name,msg)
				select r.user_no,r.user_name,'<该人员已封账>'  from bd_schedule_receive r
				inner join (
					select DISTINCT s.user_no from #scheduleTable s
					INNER JOIN bd_kq_accounts a on a.user_serial = s.user_serial and a.year =DATEPART(year, @yearMonth+'-01')  and a.month = DATEPART(month, @yearMonth+'-01')
				) b on b.user_no = r.user_no
				where r.bh = @bh

				--移除不存在的人员
				delete from #scheduleTable where user_no in (select user_no from #returnTable);


				--插入到#result(不是导入的月份过滤掉，出现该情况的原因是，数据固定31个字段，标识该月的31天，
				--但不是所有的月份都有31天,这些不该存储的日期，被计算到下月，所以要把这些排除掉)
				insert into #result(user_serial,shift_id,shift_bh,col_name,value,date,year_month,shift_is_rest)
				select
							b.user_serial,
							zb.id,
							zb.bh,
							b.col_name,
							case when zb.bh is null then b.fh else b.fh+zb.bh+'|'+b.banci_mc end ,
							b.date,
							@yearMonth,
							case when b.fh = '[X]' THEN 1 ELSE 0 END
				from #scheduleTable b
					left JOIN zt_banci zb on zb.mc = b.banci_mc
					where convert(varchar(7),b.date,120) =@yearMonth;


				--删除kt_paiba表中的相关记录
				delete k from  kt_paiba k
				INNER JOIN (select DISTINCT user_serial from #result) b on b.user_serial = k.user_serial
				where k.rq = @yearMonth;

				-- 将插入的数据存入到临时排班表中
				-- 下面两段代码将竖着的数据转为了横着的数据
				INSERT INTO kt_paiba (user_serial, rq, gly_no, year, month)
				SELECT distinct a.user_serial,@yearMonth,@operator,YEAR(date),MONTH(date)
				FROM #result a


				-- 循环拼接需要更新临时表中的字段
				-- 查询（循环）拼接数据（竖着循环）
				DECLARE @update_insert VARCHAR(MAX)=''
				SELECT @update_insert+='UPDATE kt_paiba SET '+col_name+'='''+isnull(value,'')+''',gly_no = '''+@operator+''' WHERE rq='''+year_month+''' AND user_serial='+CAST(a.user_serial AS VARCHAR(10))+';'
				FROM #result a

				-- 执行拼接的语句
				EXEC(@update_insert)


				--更新排班明细
				-- 删除排班纵向数据表
				delete a
				from bd_kq_schedule_detail a
				inner join #result b on a.user_serial = b.user_serial and convert(varchar(7) ,a.shift_date,120) = @yearMonth

				-- 插入数据详情表
				insert into bd_kq_schedule_detail(user_serial,shift_id,shift_name,shift_date,shift_begin,shift_end,shift_sign_begin,shift_sign_end,shift_late,shift_early,shift_duty_on_delay,shift_duty_off_delay,shift_workday,shift_workday_hours,is_enjoyment_subsidized,middle_shift_start_time,middle_shift_end_time,night_shift_start_time,night_shift_end_time,repast_start_time,repast_end_time,repast_start_time2,repast_end_time2,shift_is_rest)
				select a.user_serial,a.shift_id,a.value,a.date,isnull(c.sbsj,0),isnull(c.xbsj,0),isnull(c.sbks,0),isnull(c.xbjs,0),isnull(c.cdsj,0),isnull(c.ztsj,0),isnull(c.bjcd,0),isnull(c.bjzt,0),isnull(c.alld,0),isnull(c.allt,0),isnull(c.is_enjoyment_subsidized,0),isnull(c.middle_shift_start_time,0),isnull(c.middle_shift_end_time,0),isnull(c.night_shift_start_time,0),isnull(c.night_shift_end_time,0),isnull(repast_start_time,0),isnull(c.repast_end_time,0),isnull(repast_start_time2,0),isnull(c.repast_end_time2,0),a.shift_is_rest
				from #result a
				inner join zt_banci b on a.shift_id = b.id
				inner join zt_shid c on b.bh = c.parent
				where c.lx = 0

				--返回错误信息
				select
					id,
					user_no as userNo,
					user_name as userName,
					msg
				from #returnTable

				DECLARE @user_serial_str varchar(max)=''
				DECLARE @begin_date varchar(10)
				DECLARE @end_date varchar(10)


				select @user_serial_str = @user_serial_str + CAST(u.user_serial AS VARCHAR(100)) + ',' from(
				SELECT DISTINCT user_serial FROM #result) u

				SET @user_serial_str = SUBSTRING(@user_serial_str,0, LEN(@user_serial_str))

				set @begin_date = @yearMonth+'-01';
				set @end_date = convert(varchar(10),EOMONTH(@yearMonth+'-01'),120);

				-- 调用考勤计算(排班4)
				EXEC bd_proc_timecard_extend @begin_date,@end_date,@user_serial_str,4

				set @result = @@ROWCOUNT;

		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
		END CATCH


-- 打开打印受影响行数
SET NOCOUNT OFF;
END;